package com.ssy.lingxi.report.dao.contract;

import com.ssy.lingxi.report.model.bo.tally.statistics.ContractApplyAmountOrderStatisticsTallyVO;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.List;

/**
 *  合同请款DAO
 * @author ds
 * @date 2021/5/11
 * @version 2.0.0
 */
@Service
public class ApplyAmountOrderDAO {

    @Resource
    @Qualifier("contractJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    /**
     * 查询合同请款统计数量
     * @return
     */
    public List<ContractApplyAmountOrderStatisticsTallyVO> getStatusStatisticsCount() {
        StringBuilder sql = new StringBuilder();
        sql.append(" select ")
                .append(" member_id memberId,role_id roleId,")
                .append(" sum(case when status in(1,3,5) then 1 else 0 end) toBeCommitValifyCount,")
                .append(" sum(case when status=2 then 1 else 0 end) toBeValifyStep1Count,")
                .append(" sum(case when status=4 then 1 else 0 end) toBeValifyStep2Count,")
                .append(" sum(case when status=6 then 1 else 0 end) toBeCommitCount")
                .append(" FROM con_apply_amount_order ")
                .append(" group by member_id,role_id;");
        return jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(ContractApplyAmountOrderStatisticsTallyVO.class));
    }

}
